package dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import model.Dossier;
import model.Emp;
import util.DBUtil;
import util.Page;
import dao.DossierDAO;

public class DossierDAOImpl implements DossierDAO {
	DBUtil dbu = new DBUtil();
	Connection conn = null;
	PreparedStatement pstat = null;
	ResultSet rs = null;
	boolean result=false;
	
	public boolean addDossier(Dossier dossier) {

			try {
				conn = dbu.getConnection();
				String sql = "insert into dossier values(DOSSIERSEQ.nextval,?,?,?,?,?)";
				pstat = conn.prepareStatement(sql);
				pstat.setString(1, dossier.getDosno());
				pstat.setInt(2, dossier.getEmp().getId());
				pstat.setString(3, dossier.getName());
				pstat.setString(4, dossier.getSynopsis());
				pstat.setString(5, dossier.getRemark());
				int i=pstat.executeUpdate();
				if(i>0)
				{
					result=true;
				}
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				dbu.closeConnection(conn, pstat, rs);
			}
			return result;
	}

	public boolean deleteByDosNo(String dosno) {
		String sql="delete from dossier where dosno=?";
		conn=dbu.getConnection();
		try {
			pstat=conn.prepareStatement(sql);
			pstat.setString(1,dosno);
			pstat.executeUpdate();
			result=true;
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			dbu.closeConnection(conn, pstat, rs);
		}
		return result;
	}

	public boolean deleteById(int id) {
		String sql="delete from dossier where id=?";
		conn=dbu.getConnection();
		try {
			pstat=conn.prepareStatement(sql);
			pstat.setInt(1,id);
			pstat.executeUpdate();
			result=true;
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			dbu.closeConnection(conn, pstat, rs);
		}
		return result;
	}

	public boolean deleteDossier(Dossier dossier) {
		String sql="delete from Dossier where id=?";
		conn=dbu.getConnection();
		try {
			pstat=conn.prepareStatement(sql);
			pstat.setInt(1, dossier.getId());
			pstat.executeUpdate();
			result=true;
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			dbu.closeConnection(conn, pstat, rs);
		}
		return result;
	}

	public boolean deteteByEmpId(int empid) {
		String sql="delete from Dossier where empid=?";
		conn=dbu.getConnection();
		try {
			pstat=conn.prepareStatement(sql);
			pstat.setInt(1, empid);
			pstat.executeUpdate();
			result=true;
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			dbu.closeConnection(conn, pstat, rs);
		}
		return result;
	}

	public Page<Dossier> getAllDossier(int pageNo, int pageSize) {
		Page<Dossier> page = new Page<Dossier>(pageNo, pageSize);
		int totalCount = 0;
		List<Dossier> list = new ArrayList<Dossier>();
		try {
			conn = dbu.getConnection();
			String sql="select count(*) from Dossier";
			pstat=conn.prepareStatement(sql);
			rs=pstat.executeQuery();
			if (rs.next()) {
				totalCount = rs.getInt(1);
			}
			page.setTotalCount(totalCount);
			sql = "select * from (select s.*,rownum rn from (select * from Dossier) s where rownum <=?) where rn >=?";
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, page.getPageNo()*pageSize);
			pstat.setInt(2,(page.getPageNo()-1)*pageSize+1);
			rs = pstat.executeQuery();
			while (rs.next()) {
				Dossier dossier =new Dossier();
				Emp emp=new Emp();
				dossier.setId(rs.getInt("id"));
				dossier.setDosno(rs.getString("dosno"));

				emp=new EmpDAOImpl().getEmpById(rs.getInt("empid"));
				dossier.setEmp(emp);
				
				dossier.setName(rs.getString("name"));
				dossier.setSynopsis(rs.getString("synopsis"));
				dossier.setRemark(rs.getString("remark"));
				
				list.add(dossier);
				
			}
			page.setList(list);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return page;
	}

	public Dossier getByDosNo(String dosno) {
		Dossier dossier = new Dossier();
		Emp emp=new Emp();
		
		try {
			conn = dbu.getConnection();
			String sql="select * from dossier where dosno=?";
			pstat=conn.prepareStatement(sql);
			pstat.setString(1, dosno);
			rs=pstat.executeQuery();
			
			if (rs.next()) {
				dossier.setId(rs.getInt("id"));
				dossier.setDosno(rs.getString("dosno"));

				emp=new EmpDAOImpl().getEmpById(rs.getInt("empid"));
				dossier.setEmp(emp);
				
				dossier.setName(rs.getString("name"));
				dossier.setSynopsis(rs.getString("synopsis"));
				dossier.setRemark(rs.getString("remark"));
				
				return dossier;
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return null;
	}

	public Page<Dossier> getByDossierName(int pageNo, int pageSize, String name) {
		Page<Dossier> page = new Page<Dossier>(pageNo, pageSize);
		int totalCount = 0;
		List<Dossier> list = new ArrayList<Dossier>();
		try {
			conn = dbu.getConnection();
			String sql="select count(*) from Dossier";
			pstat=conn.prepareStatement(sql);
			rs=pstat.executeQuery();
			if (rs.next()) {
				totalCount = rs.getInt(1);
			}
			page.setTotalCount(totalCount);
			sql = "select * from (select d.*,rownum rn from (select * from Dossier where name=?) d where rownum <=?) where rn >=?";
			pstat = conn.prepareStatement(sql);
			pstat.setString(1, name);
			pstat.setInt(2, page.getPageNo()*pageSize);
			pstat.setInt(3,(page.getPageNo()-1)*pageSize+1);
			rs = pstat.executeQuery();
			while (rs.next()) {
				Dossier dossier =new Dossier();
				Emp emp=new Emp();
				dossier.setId(rs.getInt("id"));
				dossier.setDosno(rs.getString("dosno"));

				emp=new EmpDAOImpl().getEmpById(rs.getInt("empid"));
				dossier.setEmp(emp);
				
				dossier.setName(rs.getString("name"));
				dossier.setSynopsis(rs.getString("synopsis"));
				dossier.setRemark(rs.getString("remark"));
				
				list.add(dossier);
			}
			page.setList(list);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return page;
	}

	public Page<Dossier> getByEmp(int pageNo, int pageSize, Emp emp) {
		// TODO Auto-generated method stub
		return null;
	}

	public Page<Dossier> getByEmp(int pageNo, int pageSize, String empName) {
		Page<Dossier> page = new Page<Dossier>(pageNo, pageSize);
		int totalCount = 0;
		List<Dossier> list = new ArrayList<Dossier>();
		try {
			conn = dbu.getConnection();
			String sql="select count(*) from Dossier";
			pstat=conn.prepareStatement(sql);
			rs=pstat.executeQuery();
			if (rs.next()) {
				totalCount = rs.getInt(1);
			}
			page.setTotalCount(totalCount);
			sql = "select * from (select d.*,rownum rn from (select * from Dossier where empName=?) d where rownum <=?) where rn >=?";
			pstat = conn.prepareStatement(sql);
			pstat.setString(1, empName);
			pstat.setInt(2, page.getPageNo()*pageSize);
			pstat.setInt(3,(page.getPageNo()-1)*pageSize+1);
			rs = pstat.executeQuery();
			while (rs.next()) {
				Dossier dossier =new Dossier();
				Emp emp=new Emp();
				dossier.setId(rs.getInt("id"));
				dossier.setDosno(rs.getString("dosno"));

				emp=new EmpDAOImpl().getEmpById(rs.getInt("empid"));
				dossier.setEmp(emp);
				
				dossier.setName(rs.getString("name"));
				dossier.setSynopsis(rs.getString("synopsis"));
				dossier.setRemark(rs.getString("remark"));
				
				list.add(dossier);
			}
			page.setList(list);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return page;
	}

	public Page<Dossier> getByEmp(int pageNo, int pageSize, int empid) {
		Page<Dossier> page = new Page<Dossier>(pageNo, pageSize);
		int totalCount = 0;
		List<Dossier> list = new ArrayList<Dossier>();
		try {
			conn = dbu.getConnection();
			String sql="select count(*) from Dossier";
			pstat=conn.prepareStatement(sql);
			rs=pstat.executeQuery();
			if (rs.next()) {
				totalCount = rs.getInt(1);
			}
			page.setTotalCount(totalCount);
			sql = "select * from (select d.*,rownum rn from (select * from Dossier where empid=?) d where rownum <=?) where rn >=?";
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, empid);
			pstat.setInt(2, page.getPageNo()*pageSize);
			pstat.setInt(3,(page.getPageNo()-1)*pageSize+1);
			rs = pstat.executeQuery();
			while (rs.next()) {
				Dossier dossier =new Dossier();
				Emp emp=new Emp();
				dossier.setId(rs.getInt("id"));
				dossier.setDosno(rs.getString("dosno"));

				emp=new EmpDAOImpl().getEmpById(rs.getInt("empid"));
				dossier.setEmp(emp);
				
				dossier.setName(rs.getString("name"));
				dossier.setSynopsis(rs.getString("synopsis"));
				dossier.setRemark(rs.getString("remark"));
				list.add(dossier);
			}
			page.setList(list);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return page;
	}

	public Dossier getById(int id) {
		Dossier dossier = new Dossier();
		Emp emp=new Emp();
		
		try {
			conn = dbu.getConnection();
			String sql="select * from dossier where id=?";
			pstat.setInt(1, id);
			pstat=conn.prepareStatement(sql);
			rs=pstat.executeQuery();
			
			if (rs.next()) {
				dossier.setId(rs.getInt("id"));
				dossier.setDosno(rs.getString("dosno"));

				emp=new EmpDAOImpl().getEmpById(rs.getInt("empid"));
				dossier.setEmp(emp);
				
				dossier.setName(rs.getString("name"));
				dossier.setSynopsis(rs.getString("synopsis"));
				dossier.setRemark(rs.getString("remark"));
				return dossier;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return null;
	}

	public boolean updateDossier(Dossier dossier) {
		try {
			conn = dbu.getConnection();
			String sql="update dossier set dosno=?,name=?,empid=?,synopsis=?,remark=? where id=?";
			pstat=conn.prepareStatement(sql);
			pstat.setString(1, dossier.getDosno());
			pstat.setString(2, dossier.getName());
			pstat.setInt(3, dossier.getEmp().getId());
			pstat.setString(4, dossier.getSynopsis());
			pstat.setString(5, dossier.getRemark());
			pstat.setInt(6, dossier.getId());

			if(pstat.executeUpdate()>0){
				result=true;
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return result;
	}

}